package com.hbnu.dao;

import com.hbnu.entity.SystemWordBook;
import com.hbnu.entity.Word;
import com.hbnu.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SystemWordBookDAO {
    // 查询所有系统词书
    public List<SystemWordBook> getAllSystemBooks() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<SystemWordBook> bookList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM system_word_book ORDER BY id";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                SystemWordBook book = new SystemWordBook();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("name"));
                book.setDescription(rs.getString("description"));
                book.setWordCount(rs.getInt("word_count"));
                bookList.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
//            DBUtil.close(conn, pstmt, rs);
        }
        return bookList;
    }

    // 根据词书ID查询单词
    public List<Word> getWordsBySystemBookId(int bookId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Word> wordList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT w.* FROM word w JOIN system_book_word sbw ON w.id = sbw.word_id WHERE sbw.system_book_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, bookId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Word word = new Word();
                word.setId(rs.getInt("id"));
                word.setWord(rs.getString("word"));
                word.setPhonetic(rs.getString("phonetic"));
                word.setDefinition(rs.getString("definition"));
                word.setExample(rs.getString("example"));
                wordList.add(word);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        return wordList;
    }
}
